package com.qidian.hw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.qidian.hw.dao.ServiceInfoDAO;
import com.qidian.hw.pojo.ServiceInfo;
import com.qidian.hw.tools.FormatUtil;

/**
* @author 郭浩
*
*@company 起点编程
*
* 2022年8月19日 下午4:47:57
*/
public class ServiceInfoDAOImpl extends BaseDAO implements ServiceInfoDAO{
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	public ArrayList<ServiceInfo> serviceInfoList() {
		ArrayList<ServiceInfo> al = new ArrayList<ServiceInfo>();
		String sql = "select * from service_info where status = 1";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				ServiceInfo serviceInfo = new ServiceInfo();
				serviceInfo.setServiceId(rs.getLong("service_id"));
				serviceInfo.setServiceTitle(rs.getString("service_title"));
				serviceInfo.setServiceDetails(rs.getString("service_details"));
				serviceInfo.setServiceImg(rs.getString("service_img"));
				serviceInfo.setCreateTime(rs.getString("create_time"));
				serviceInfo.setModifyTime(rs.getString("modify_time"));
				al.add(serviceInfo);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs,pstmt,con);
		}
		return al;
	}

	public ServiceInfo queryServiceInfoById(long id) {
		ServiceInfo serviceInfo = null;
		String sql = "select * from service_info where service_id = ?";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				serviceInfo = new ServiceInfo();
				serviceInfo.setServiceTitle(rs.getString("service_title"));
				serviceInfo.setServiceDetails(rs.getString("service_details"));
				serviceInfo.setServiceImg(rs.getString("service_img"));
				serviceInfo.setCreateTime(rs.getString("create_time"));
				serviceInfo.setModifyTime(rs.getString("modify_time"));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs,pstmt,con);
		}
		return serviceInfo;
	}

	public int serviceInfoUpdate(ServiceInfo serviceInfo) {
		int result = 0;
		String sql = "update service_info set service_title = ?,service_details = ?,service_img = ?,modify_time = ? where service_id = ?";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, serviceInfo.getServiceTitle());
			pstmt.setString(2, serviceInfo.getServiceDetails());
			pstmt.setString(3, serviceInfo.getServiceImg());
			String date = FormatUtil.getNow();
			pstmt.setString(4, date);
			pstmt.setLong(5, serviceInfo.getServiceId());
			result = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, con);
		}
		return result;
	}

	public int serviceInfoDelete(long id) {
		int result = 0;
		String sql = "update service_info set modify_time = ?,status = 0 where service_id = ?";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			String date = FormatUtil.getNow();
			pstmt.setString(1, date);
			pstmt.setLong(2, id);
			result = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, con);
		}
		return result;
	}

	public int serviceInfoSave(ServiceInfo serviceInfo) {
		int result = 0;
		String sql = "insert into service_info(service_title,service_details,service_img,create_time,modify_time) values(?,?,?,?,?)";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, serviceInfo.getServiceTitle());
			pstmt.setString(2, serviceInfo.getServiceDetails());
			pstmt.setString(3, serviceInfo.getServiceImg());
			String date = FormatUtil.getNow();
			pstmt.setString(4, date);
			pstmt.setString(5, date);
			result = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, con);
		}
		return result;
	}

	public int count(String title) {
		int result = 0;
		String sql = "select count(*) from service_info where status = 1  ";
		// 拼接条件
		if (FormatUtil.isNull(title)) {
			sql += " and service_title like '%" + title + "%'";
		}
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, con);
		}
		return result;
	}

	public ArrayList<ServiceInfo> queryByPage(int page, int size, String title) {
		ArrayList<ServiceInfo> serviceInfos = new ArrayList<ServiceInfo>();
		String sql = "select * from service_info where status = 1 ";
		// 拼接条件
		if (FormatUtil.isNull(title)) {
			sql += " and service_title like '%" + title + "%'";
		}
		sql += " limit "+((page-1)*size)+","+size;
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				ServiceInfo serviceInfo = new ServiceInfo();
				serviceInfo.setServiceId(rs.getLong("service_id"));
				serviceInfo.setServiceTitle(rs.getString("service_title"));
				serviceInfo.setServiceDetails(rs.getString("service_details"));
				serviceInfo.setServiceImg(rs.getString("service_img"));
				serviceInfo.setCreateTime(rs.getString("create_time"));
				serviceInfo.setModifyTime(rs.getString("modify_time"));
				serviceInfos.add(serviceInfo);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, con);
		}
		return serviceInfos;
	}

	public int deletes(String[] ids) {
		int result = 0;
		String sql = "update service_info set status = 0 where service_id in(";
		for(int i = 0;i < ids.length;i ++) {
			sql += ids[i] + ",";
		}
		sql += "0)";
		try {
			con = getCon();
			pstmt = con.prepareStatement(sql);
			result = pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, con);
		}
		return result;
	}
}
